package com.qdu.dao;

import com.qdu.model.Student;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Dao-Data Acess Object 通常会构建Dao对象，实现数据库表的增删改查
 * 
 * @author Annabelle
 */
public class StudentDao {
    
     public List<Student> findAllByBatch(String batchName) {

//        String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//        String url = "jdbc:sqlserver://localhost:1433;databaseName=QDU";
//        String username = "sa";
//        String password = "sa";

        String driverClass = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/您的数据库?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8";
        String username = "用户名";
        String password = "密码";

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<>();

        try {
            Class.forName(driverClass);
            con = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            ps = con.prepareStatement("select * from Student where sbatch=?");
            ps.setString(1, batchName);
            rs = ps.executeQuery();
            while (rs.next()) { //next()判断有没有下一行，并移动到下一行
                String sid = rs.getString(1);
                String spwd = rs.getString(2);
                String sname = rs.getString(3);
                String sgender = rs.getString(4);
                String sbatch = rs.getString(5);
                list.add(new Student(sid, spwd, sname, sgender, sbatch));
            }
            return list;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != ps) {
                    ps.close();
                }
                if (null != con) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(StudentDao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return null;
    }

    /**
     * 根据学号查询单个学生的所有信息
     * 
     * @param sid 字符串表示的学生学号
     * @return 一个Student对象，包含了要查询的学生的所有5项信息
     */
    public Student findOneById(String sid) {

//        String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//        String url = "jdbc:sqlserver://localhost:1433;databaseName=QDU";
//        String username = "sa";
//        String password = "sa";

        String driverClass = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/qdu?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8";
        String username = "root";
        String password = "root";

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student s = null;

        try {
            Class.forName(driverClass);
            con = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            ps = con.prepareStatement("select * from Student where sid=?");
            ps.setString(1, sid);
            rs = ps.executeQuery();
            if (rs.next()) { //next()判断有没有下一行，并移动到下一行
                String spwd = rs.getString(2);
                String sname = rs.getString(3);
                String sgender = rs.getString(4);
                String sbatch = rs.getString(5);
                s = new Student(sid, spwd, sname, sgender, sbatch);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != ps) {
                    ps.close();
                }
                if (null != con) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(StudentDao.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return s;
    }
}
